package fashionshop.dal;

import java.sql.ResultSet;

import database.MySQLConnect;
import fashionshop.bll.OrderBLL;
import fashionshop.bll.OrderDetailBLL;

public class OrderDetailDAL {
	
	public OrderDetailDAL(){
	}
	
	public static ResultSet LoadByOrderID(int orderID) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT order_detail.*, product.Name, product.Code FROM order_detail, product WHERE order_detail.ProductID = product.ID and order_detail.OrderID = '%d'";
		String sqlQuery = String.format(sql, orderID);
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByOrderIDProductID(int orderID, int productID) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT order_detail.*, product.Name, product.Code FROM order_detail, product WHERE order_detail.ProductID = product.ID and order_detail.OrderID = '%d' and order_detail.ProductID = '%d'";
		String sqlQuery = String.format(sql, orderID, productID);
		return conn.executeQuery(sqlQuery);
	}
	
	public static int Insert(OrderDetailBLL orderDetail) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "INSERT INTO order_detail (`ProductID`, `OrderID`, `Price`, `Quantity`, `Sum`) VALUES ('%d', '%d', '%.0f', '%d',  '%.0f')";
		String sqlQuery = String.format(sql, orderDetail.getProductID(), orderDetail.getOrderID(), orderDetail.getPrice(), orderDetail.getQuantity(), orderDetail.getSum());
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int Update(OrderDetailBLL orderDetail) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE order_detail SET `Price`='%.0f', `Quantity`='%d', `Sum`='%.0f' WHERE `ProductID`='%d' and`OrderID`='%d'";
		String sqlQuery = String.format(sql, orderDetail.getPrice(), orderDetail.getQuantity(), orderDetail.getSum(), orderDetail.getProductID(), orderDetail.getOrderID());
		return conn.executeUpdate(sqlQuery);
	}

	public static int Delete(OrderDetailBLL orderDetail) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "DELETE FROM `fashion_shop`.`order_detail` WHERE `ProductID`='%d' and`OrderID`='%d'";
		String sqlQuery = String.format(sql, orderDetail.getProductID(), orderDetail.getOrderID());
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int DeleteAll(int orderID) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "DELETE FROM `fashion_shop`.`order_detail` WHERE `OrderID`='%d'";
		String sqlQuery = String.format(sql, orderID);
		return conn.executeUpdate(sqlQuery);
	}
}
